﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace MyDAL
{
    /// <summary>
    /// 读取Excel类
    /// </summary>
    public class ExcelDataBase
    {
        string _strConn;

        public string StrConn
        {
            get { return _strConn; }
        }
        private string _path;

        public string Path
        {
            get { return _path; }
           
        }

        public ExcelDataBase(string path)
        {
            _strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                    "Data Source=" + path + ";" +
                    "Extended Properties=Excel 8.0;";
        }
        /// <summary>
        /// 读取Excml数据
        /// </summary>
        /// <param name="pageName">当前查询的页(空的话为默认值)</param>
        /// <param name="condition">查询条件</param>
        /// <returns>返回数据集</returns>
        public DataSet SelectExcel(string pageName,string condition)
        {
            OleDbConnection conn = new OleDbConnection(_strConn);
            string cmd;
            if (pageName == null || pageName =="")
            {
                cmd = "select * FROM [sheet1$] " + condition;
            }
            else
            {
                cmd = "select * FROM [" + pageName + "$] " + condition;
            }
            OleDbDataAdapter myCommand = new OleDbDataAdapter(cmd, _strConn);//distinct
            DataSet myDataSet = new DataSet();
            myCommand.Fill(myDataSet);
            return myDataSet;
        }

        /// <summary>
        /// 读取Excml数据
        /// </summary>
        /// <param name="pageName">当前查询的页(空的话为默认值)</param>
        /// <param name="condition">查询条件</param>
        /// <returns>返回数据集</returns>
        public DataSet SelectExcel(string cmd)
        {
            OleDbConnection conn = new OleDbConnection(_strConn);
            OleDbDataAdapter myCommand = new OleDbDataAdapter(cmd, _strConn);//distinct
            DataSet myDataSet = new DataSet();
            myCommand.Fill(myDataSet);
            return myDataSet;
        }
    }
}
